-- Function: tracker_system.data_entry_qc_ets_get_all_qc_detail(integer, date, date)

-- DROP FUNCTION tracker_system.data_entry_qc_ets_get_all_qc_detail(integer, date, date);

CREATE OR REPLACE FUNCTION tracker_system.data_entry_qc_ets_get_all_qc_detail(integer, date, date)
  RETURNS integer AS
$BODY$
DECLARE 
	v_project_id alias FOR $1;	
	v_exp_start_time alias for $2;
	v_exp_end_time alias for $3;
	v_column_name_key record;
	v_str_column_name_key character varying(255) := '';
	v_str_column_name_key_select character varying(255) := '';
	v_column_name_error record;
	v_str_column_name_error character varying(255) := '';
	v_str_column_name_error_select character varying(255) := '';
	v_strQuery character varying (40000) :='';
	--v_project_id int;
BEGIN

--select tracker_system.data_entry_qc_ets_get_all_qc_detail(562, '2013-12-01', '2013-12-30')

--v_project_id := (select project_id from tracker_system.project where cls_project_id =v_cls_project_id);
-- colum key

	FOR v_column_name_key 
		IN SELECT distinct c.column_name_map FROM tracker_system.template_column a, tracker_system.project b ,
					tracker_system.project_column_keyname c
				      WHERE a.template_id = b.template_id and b.project_id = v_project_id and a.is_key = true
				      and b.project_id = c.project_id and a.column_name = c.column_name
	        LOOP	
		          v_str_column_name_key := v_str_column_name_key||' '||v_column_name_key||' text,';
		          v_str_column_name_key_select := v_str_column_name_key_select||' '||v_column_name_key||',';
	END LOOP;
	if(v_str_column_name_key <> '' and v_str_column_name_key_select <> '') THEN
	raise notice '%',v_str_column_name_key;
	----------------------------------change
		v_str_column_name_key := replace(replace(replace(v_str_column_name_key,'"',''),'(','("'),')','")');
		v_str_column_name_key_select := replace(replace(replace(v_str_column_name_key_select,'"',''),'(','("'),')','")');
		----------------------------------- change
		v_str_column_name_key := substring((replace(replace(v_str_column_name_key,'(',''),')','')),1,length((replace(replace(v_str_column_name_key,'(',''),')','')))-1);
		v_str_column_name_key_select := substring((replace(replace(v_str_column_name_key_select,'(',''),')','')),1,length((replace(replace(v_str_column_name_key_select,'(',''),')','')))-1);
		raise notice '%',v_str_column_name_key;
	--- column error
		FOR v_column_name_error 
			IN SELECT distinct column_name FROM tracker_system.template_column a, tracker_system.project b 
					      WHERE a.template_id = b.template_id and b.project_id = v_project_id and a.is_key = false
			LOOP	
				  v_str_column_name_error := v_str_column_name_error||' '||v_column_name_error||' text,';
				  v_str_column_name_error_select := v_str_column_name_error_select||' '||v_column_name_error||' ,';
		END LOOP;
		--raise notice '%',v_str_column_name_error;
	---------------------------- change
		v_str_column_name_error := replace(replace(replace(v_str_column_name_error,'"',''),'(','("'),')','")');
		v_str_column_name_error_select := replace(replace(replace(v_str_column_name_error_select,'"',''),'(','("'),')','")');
	----------------------------chang
		v_str_column_name_error := substring((replace(replace(v_str_column_name_error,'(',''),')','')),1,length((replace(replace(v_str_column_name_error,'(',''),')','')))-1);
		v_str_column_name_error_select := substring((replace(replace(v_str_column_name_error_select,'(',''),')','')),1,length((replace(replace(v_str_column_name_error_select,'(',''),')','')))-1);
		--raise notice '%',v_str_column_name_error;
		DROP table if exists dataentry_ets; 	
		v_strQuery := '
		create table dataentry_ets as 
		SELECT a.array_name[1] as qc_card_id, a.array_name[2] as qc_date, a.array_name[3] as qc_user,a.array_name[4] as export_date, a.array_name[5]::int as export_times,
		'||v_str_column_name_key_select||'  FROM public.crosstab
		
				(''select ARRAY[q.qc_card_id::text, t.qc_date::text, q.qc_user , t.export_date::text,t.export_times::text] As array_name, d.column_name_map , k.column_value
			from tracker_system.tracker_info t, tracker_system.qc_card q, tracker_system.key_value k,tracker_system.template_column c,
			tracker_system.project_column_keyname d
			where t.project_id = d.project_id and c.column_name = d.column_name and t.project_id= '||v_project_id||' AND
			      t.approve_status = true AND
			      
			      TO_DATE(t.qc_date,''''dd.MM.yyyy'''') >='''''||v_exp_start_time||''''' AND
			      TO_DATE(t.qc_date,''''dd.MM.yyyy'''') <='''''||v_exp_end_time||''''' AND
			      t.tracker_info_id = q.tracker_info_id AND
			      q.qc_card_id = k.qc_card_id AND
			      c.column_id = k.column_id AND
			      c.is_key=true
			      Order by q.qc_card_id
			      ;'', ''SELECT distinct column_name_map FROM tracker_system.template_column a, tracker_system.project b,tracker_system.project_column_keyname c
					      WHERE b.project_id = c.project_id and lower(a.column_name) = lower(c.column_name) 
					      and a.template_id = b.template_id and b.project_id = '||v_project_id||' and a.is_key = true'')
			as a(array_name text[],'||v_str_column_name_key||')';	
		raise notice '%',v_strQuery;	
		execute v_strQuery;
	End if;
	return 1;
END;	
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tracker_system.data_entry_qc_ets_get_all_qc_detail(integer, date, date)
  OWNER TO postgres;

